package com.example.rtmnanage;
import android.util.Log;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.bean.FoodinfoSmall;
import com.example.bean.MenuButton;
import com.example.bean.OrderInfo;
import com.example.bean.ShopDesk;
import com.example.utils.Constant;

/**
 * Created by Weili on 2017/7/14.
 */

public class MysqlDb {
    final static String DRIVER_NAME = "com.mysql.jdbc.Driver";
    public static Connection openConnection(String url, String user,
                                            String password) {
        Connection conn = null;
        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            conn = null;
        } catch (SQLException e) {
            conn = null;
        }

        return conn;
    }

    /**
     * 鏌ヨ
     * @param conn 杩炴帴瀵硅薄
     * @param sql 鏌ヨ璇彞
     * @return
     */
    public static  String   query(Connection conn, String sql) {
        String isNull = null;
        if (conn == null) {
            return null;
        }
        Statement statement = null;
        ResultSet result = null;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            while(result.next()){//灏嗙粨鏋滈泦淇℃伅娣诲姞鍒拌繑鍥炲悜閲忎腑
                isNull=result.getString("order_id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   isNull;
    }

    public static boolean execSQL(Connection conn, String sql) {
        boolean execResult = false;
        if (conn == null) {
            return execResult;
        }
        Statement statement = null;
        try {
            statement = conn.createStatement();
            if (statement != null) {
                execResult = statement.execute(sql);
            }
        } catch (SQLException e) {
            execResult = false;
        }
        return execResult;
    }


    /**
     * 鏌ヨ宸︿晶鑿滃崟
     * @param conn 杩炴帴瀵硅薄
     * @param sql  sql璇彞
     * @return  杩斿洖涓�涓狶ist
     */
    public static  List<MenuButton>   selectCuisine(Connection conn, String sql) {
        List<MenuButton>  list=  new ArrayList<MenuButton>();

        if (conn == null) {
            return null;
        }
        Statement statement = null;
        ResultSet result = null;
//        list=null;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            while(result.next()){//灏嗙粨鏋滈泦淇℃伅娣诲姞鍒拌繑鍥炲悜閲忎腑
                String cuisine_name =result.getString("cuisine_name");
                String cuisine_describe =result.getString("cuisine_describe");
                String   describe_id = result.getString("describe_id");
                String   describe_version = result.getString("describe_version");

                list.add(   new MenuButton(0,cuisine_name,cuisine_describe,describe_id,describe_version));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   list;
    }

    public static  String   selectDeskNO(Connection conn, String sql) {
    	
    	String  status =null;
        if (conn == null) {
            return (String) null;
        }
        Statement statement = null;
        ResultSet result = null;
//        list=null;
        int a=0;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            if (result.next()) {  
            	 //存在记录 rs就要向上移一条记录 因为rs.next会滚动一条记录了  
            	result.previous();  
            	 //在执行while 循环  
            	 while(result.next()){  
            		 status=result.getString(Constant.SHOP_STATUS);
            	}
            }
            	                                  
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   status;
    }
    /**
     * 查询最近的
     * @param conn
     * @param sql
     * @return
     */  
 public static  String   selectOrderTemp(Connection conn, String sql) {
    	
    	String  status =null;
        if (conn == null) {
            return (String) null;
        }
        Statement statement = null;
        ResultSet result = null;
//        list=null;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            if (result.next()) {  
            	 //存在记录 rs就要向上移一条记录 因为rs.next会滚动一条记录了  
            	result.previous();  
            	 //在执行while 循环  
            	 while(result.next()){  
            		 status=result.getString(Constant.ORDERID);
            	}
            }
            	                                  
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   status;
    }
 
 /**
  * 新增桌台
  * @param conn
  * @param sql
  * @return
  */
 public static  int   addTempDesk(Connection conn, String sql) { 

	
  if (conn == null) {
      return  (Integer) null;
  }
  Statement statement = null;
  int result = 0;
//  list=null;

  try {
      statement = conn.createStatement();
      result = statement.executeUpdate(sql);
     if(result!=-1){
  	   result=1;
     }
      	                                  
  } catch (SQLException e) {
      e.printStackTrace();
  } finally {
      try {             
          if (statement != null) {
              statement.close();
              statement = null;
          }

      } catch (SQLException sqle) {
          sqle.printStackTrace();
      }
  }
  return   result;
}
  
 /**
  * 修改临时表
  * @param conn
  * @param sql
  * @return
  */
 public static  int   upddateTemp(Connection conn, String sql) { 
	
   if (conn == null) {
       return  (Integer) null;
   }
   Statement statement = null;
   int result = 0;
//   list=null;

   try {
       statement = conn.createStatement();
       result = statement.executeUpdate(sql);
      if(result!=-1){
   	   result=1;
      }
       	                                  
   } catch (SQLException e) {
       e.printStackTrace();
   } finally {
       try {             
           if (statement != null) {
               statement.close();
               statement = null;
           }

       } catch (SQLException sqle) {
           sqle.printStackTrace();
       }
   }
   return   result;
}
    
 
 
 /**
  * 查询所有桌台
  * @param conn
  * @param sql
  * @return
  */
 public static  List<ShopDesk>   sleectAllDesk(Connection conn, String sql) {    
	 Log.d("MysqlDb", "desk_status"+sql);
	 
     List<ShopDesk>  list=  new ArrayList<ShopDesk>();
     if (conn == null) {
         return null;
     }
     Statement statement = null;
     ResultSet result = null;
//     list=null;
     try {
         statement = conn.createStatement();
         result = statement.executeQuery(sql);
         int i=1;
         while(result.next()){
        	   i++;
             String food_name =result.getString(Constant.DESK_NO);
             String desk_status =result.getString(Constant.DESK_STATUS);
       
             if(desk_status==null||desk_status.equals("")||desk_status.equals(null)){
            	 desk_status=Constant.STATUS_WAIT;
             }
         
             list.add(   new ShopDesk(i,food_name,desk_status));
             
         }
     } catch (SQLException e) {
         e.printStackTrace();
     } finally {
         try {
             if (result != null) {
                 result.close();
                 result = null;
             }
             if (statement != null) {
                 statement.close();
                 statement = null;
             }

         } catch (SQLException sqle) {
             sqle.printStackTrace();
         }
     }
     return   list;
 }
    /**
     * 进行开台
     * @param conn
     * @param sql
     * @return
     */
    public static  int   startDeskNO(Connection conn, String sql) { 
    	
        if (conn == null) {
            return  (Integer) null;
        }
        Statement statement = null;
        int result = 0;
//        list=null;
   
        try {
            statement = conn.createStatement();
            result = statement.executeUpdate(sql);
           if(result!=-1){
        	   result=1;
           }
            	                                  
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {             
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   result;
    }


    /**
     * 鏌ヨ琛�
     * @param conn
     * @param sql
     * @return
     */
    public static  List<FoodinfoSmall>   selectFood(Connection conn, String sql) {
       
        List<FoodinfoSmall>  list=  new ArrayList<FoodinfoSmall>();
        if (conn == null) {
            return null;
        }
        Statement statement = null;
        ResultSet result = null;
//        list=null;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            while(result.next()){//灏嗙粨鏋滈泦淇℃伅娣诲姞鍒拌繑鍥炲悜閲忎腑
                String food_name =result.getString("food_name");
                String food_describe =result.getString("food_describe");
                double   food_Price = result.getDouble("food_Price");
                String   food_image = result.getString("food_image");
                list.add(   new FoodinfoSmall(0,food_name,food_describe,food_Price,food_image));
           
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   list;
    }
    public static  List<FoodinfoSmall>   ByPageIndex(Connection conn, String tableName ,int  pageIndex ,int pageSize ) {

        int index = (pageIndex-1) * pageSize;
        String  sql   ="select * from  "+tableName+" limit "+index+","+pageSize+"";
       
        List<FoodinfoSmall>  list=  new ArrayList<FoodinfoSmall>();
        if (conn == null) {
            return null;
        }
        Statement statement = null;
        ResultSet result = null;
//        list=null;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            while(result.next()){//灏嗙粨鏋滈泦淇℃伅娣诲姞鍒拌繑鍥炲悜閲忎腑
                String food_name =result.getString("food_name");
                String food_describe =result.getString("food_describe");
                double   food_Price = result.getDouble("food_Price");
                String   food_image = result.getString("food_image");

                list.add(   new FoodinfoSmall(0,food_name,food_describe,food_Price,food_image));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   list;
    }
    public static  List<OrderInfo>   selectRiht(Connection conn, String sql) {
        List<OrderInfo> newList=new ArrayList<OrderInfo>(); ;
      
        int count=1;
        if (conn == null) {
            return null;
        }
        Statement statement = null;
        ResultSet result = null;
        try {
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            while (result.next()){
                //                String data=rs.getString("date");
//                String time=rs.getString("time");
//                String desk_no=rs.getString("desk_no");
//                String consumptionID=rs.getString("consumptionID");
                String foodName=result.getString("foodName");
                double foodPrice=result.getDouble("foodPrice");
                int foodCount=result.getInt("foodCount");
                OrderInfo info=new OrderInfo(count++,foodName,Double.valueOf(foodPrice),foodCount,false);
                newList.add(info);

            }
           
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return   newList;
    }


    /**
     * 进行增删改查操作，用于判断操作成功
     *
     * @param conn
     * @param sql
     * @return
     */
    public static int exuqueteUpdate(Connection conn, String sql) {
        Log.d("sql", sql);
        if (conn == null) {
            return (Integer) null;
        }
        Statement statement = null;
        int result = 0;
//        list=null;
        try {
            statement = conn.createStatement();
            Log.d("MysqlDb", sql);

            result = statement.executeUpdate(sql);
            int a = result;
            Log.d("MysqlDb", "a:" + a);
            if (result != -1) {
                result = 1;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        return result;
    }
}